{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# 特征工程 on 数据集 Rental Listing Inquiries\n",
    "Rental Listing Inquiries 数据集是 Kaggle 平台上的一个分类竞赛任务，需要根据公寓的特征来预测其受欢迎程度（用户感兴趣程度分为高、中、低三类）。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "其中房屋的特征 X 共有 14 维，响应值 y 为用户对该公寓的感兴趣程度。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "评价标准为 logloss 。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "数据链接：https://www.kaggle.com/c/two-sigma-connect-rental-listing-inquiries"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 导入必要的工具, 用于文本特征提取 / 特征编码"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "# 用于计算 feature 字段的文本特征提取\n",
    "from sklearn.feature_extraction.text import CountVectorizer # CountVectorizer旨在通过计数来将一个文档转换为向量\n",
    "\n",
    "# CountVectorizer为稀疏特征, 特征结果编码为稀疏矩阵, xgboost处理更高效\n",
    "from scipy import sparse\n",
    "\n",
    "# 对类别型特征进行编码\n",
    "from sklearn.preprocessing import LabelEncoder\n",
    "\n",
    "# 对地理位置通过聚类进行离散化\n",
    "from sklearn.cluster import KMeans # K近邻\n",
    "from nltk.metrics import distance as distance # 距离\n",
    "\n",
    "# 可视化\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "from IPython.display import display\n",
    "pd.set_option('display.float_format', lambda x: '%.3f' % x)\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 读入数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dpath = '/Users/qi/RentalListingInquiries'\n",
    "train = pd.read_json('RentListingInquries_train.json')\n",
    "test = pd.read_json('RentListingInquries_test.json')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据探索分析"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>bathrooms</th>\n",
       "      <th>bedrooms</th>\n",
       "      <th>building_id</th>\n",
       "      <th>created</th>\n",
       "      <th>description</th>\n",
       "      <th>display_address</th>\n",
       "      <th>features</th>\n",
       "      <th>interest_level</th>\n",
       "      <th>latitude</th>\n",
       "      <th>listing_id</th>\n",
       "      <th>longitude</th>\n",
       "      <th>manager_id</th>\n",
       "      <th>photos</th>\n",
       "      <th>price</th>\n",
       "      <th>street_address</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1.500</td>\n",
       "      <td>3</td>\n",
       "      <td>53a5b119ba8f7b61d4e010512e0dfc85</td>\n",
       "      <td>2016-06-24 07:54:24</td>\n",
       "      <td>A Brand New 3 Bedroom 1.5 bath ApartmentEnjoy ...</td>\n",
       "      <td>Metropolitan Avenue</td>\n",
       "      <td>[]</td>\n",
       "      <td>medium</td>\n",
       "      <td>40.715</td>\n",
       "      <td>7211212</td>\n",
       "      <td>-73.942</td>\n",
       "      <td>5ba989232d0489da1b5f2c45f6688adc</td>\n",
       "      <td>[https://photos.renthop.com/2/7211212_1ed4542e...</td>\n",
       "      <td>3000</td>\n",
       "      <td>792 Metropolitan Avenue</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10000</th>\n",
       "      <td>1.000</td>\n",
       "      <td>2</td>\n",
       "      <td>c5c8a357cba207596b04d1afd1e4f130</td>\n",
       "      <td>2016-06-12 12:19:27</td>\n",
       "      <td></td>\n",
       "      <td>Columbus Avenue</td>\n",
       "      <td>[Doorman, Elevator, Fitness Center, Cats Allow...</td>\n",
       "      <td>low</td>\n",
       "      <td>40.795</td>\n",
       "      <td>7150865</td>\n",
       "      <td>-73.967</td>\n",
       "      <td>7533621a882f71e25173b27e3139d83d</td>\n",
       "      <td>[https://photos.renthop.com/2/7150865_be3306c5...</td>\n",
       "      <td>5465</td>\n",
       "      <td>808 Columbus Avenue</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100004</th>\n",
       "      <td>1.000</td>\n",
       "      <td>1</td>\n",
       "      <td>c3ba40552e2120b0acfc3cb5730bb2aa</td>\n",
       "      <td>2016-04-17 03:26:41</td>\n",
       "      <td>Top Top West Village location, beautiful Pre-w...</td>\n",
       "      <td>W 13 Street</td>\n",
       "      <td>[Laundry In Building, Dishwasher, Hardwood Flo...</td>\n",
       "      <td>high</td>\n",
       "      <td>40.739</td>\n",
       "      <td>6887163</td>\n",
       "      <td>-74.002</td>\n",
       "      <td>d9039c43983f6e564b1482b273bd7b01</td>\n",
       "      <td>[https://photos.renthop.com/2/6887163_de85c427...</td>\n",
       "      <td>2850</td>\n",
       "      <td>241 W 13 Street</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100007</th>\n",
       "      <td>1.000</td>\n",
       "      <td>1</td>\n",
       "      <td>28d9ad350afeaab8027513a3e52ac8d5</td>\n",
       "      <td>2016-04-18 02:22:02</td>\n",
       "      <td>Building Amenities - Garage - Garden - fitness...</td>\n",
       "      <td>East 49th Street</td>\n",
       "      <td>[Hardwood Floors, No Fee]</td>\n",
       "      <td>low</td>\n",
       "      <td>40.754</td>\n",
       "      <td>6888711</td>\n",
       "      <td>-73.968</td>\n",
       "      <td>1067e078446a7897d2da493d2f741316</td>\n",
       "      <td>[https://photos.renthop.com/2/6888711_6e660cee...</td>\n",
       "      <td>3275</td>\n",
       "      <td>333 East 49th Street</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100013</th>\n",
       "      <td>1.000</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>2016-04-28 01:32:41</td>\n",
       "      <td>Beautifully renovated 3 bedroom flex 4 bedroom...</td>\n",
       "      <td>West 143rd Street</td>\n",
       "      <td>[Pre-War]</td>\n",
       "      <td>low</td>\n",
       "      <td>40.824</td>\n",
       "      <td>6934781</td>\n",
       "      <td>-73.949</td>\n",
       "      <td>98e13ad4b495b9613cef886d79a6291f</td>\n",
       "      <td>[https://photos.renthop.com/2/6934781_1fa4b41a...</td>\n",
       "      <td>3350</td>\n",
       "      <td>500 West 143rd Street</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        bathrooms  bedrooms                       building_id  \\\n",
       "10          1.500         3  53a5b119ba8f7b61d4e010512e0dfc85   \n",
       "10000       1.000         2  c5c8a357cba207596b04d1afd1e4f130   \n",
       "100004      1.000         1  c3ba40552e2120b0acfc3cb5730bb2aa   \n",
       "100007      1.000         1  28d9ad350afeaab8027513a3e52ac8d5   \n",
       "100013      1.000         4                                 0   \n",
       "\n",
       "                    created  \\\n",
       "10      2016-06-24 07:54:24   \n",
       "10000   2016-06-12 12:19:27   \n",
       "100004  2016-04-17 03:26:41   \n",
       "100007  2016-04-18 02:22:02   \n",
       "100013  2016-04-28 01:32:41   \n",
       "\n",
       "                                              description  \\\n",
       "10      A Brand New 3 Bedroom 1.5 bath ApartmentEnjoy ...   \n",
       "10000                                                       \n",
       "100004  Top Top West Village location, beautiful Pre-w...   \n",
       "100007  Building Amenities - Garage - Garden - fitness...   \n",
       "100013  Beautifully renovated 3 bedroom flex 4 bedroom...   \n",
       "\n",
       "            display_address  \\\n",
       "10      Metropolitan Avenue   \n",
       "10000       Columbus Avenue   \n",
       "100004          W 13 Street   \n",
       "100007     East 49th Street   \n",
       "100013    West 143rd Street   \n",
       "\n",
       "                                                 features interest_level  \\\n",
       "10                                                     []         medium   \n",
       "10000   [Doorman, Elevator, Fitness Center, Cats Allow...            low   \n",
       "100004  [Laundry In Building, Dishwasher, Hardwood Flo...           high   \n",
       "100007                          [Hardwood Floors, No Fee]            low   \n",
       "100013                                          [Pre-War]            low   \n",
       "\n",
       "        latitude  listing_id  longitude                        manager_id  \\\n",
       "10        40.715     7211212    -73.942  5ba989232d0489da1b5f2c45f6688adc   \n",
       "10000     40.795     7150865    -73.967  7533621a882f71e25173b27e3139d83d   \n",
       "100004    40.739     6887163    -74.002  d9039c43983f6e564b1482b273bd7b01   \n",
       "100007    40.754     6888711    -73.968  1067e078446a7897d2da493d2f741316   \n",
       "100013    40.824     6934781    -73.949  98e13ad4b495b9613cef886d79a6291f   \n",
       "\n",
       "                                                   photos  price  \\\n",
       "10      [https://photos.renthop.com/2/7211212_1ed4542e...   3000   \n",
       "10000   [https://photos.renthop.com/2/7150865_be3306c5...   5465   \n",
       "100004  [https://photos.renthop.com/2/6887163_de85c427...   2850   \n",
       "100007  [https://photos.renthop.com/2/6888711_6e660cee...   3275   \n",
       "100013  [https://photos.renthop.com/2/6934781_1fa4b41a...   3350   \n",
       "\n",
       "                 street_address  \n",
       "10      792 Metropolitan Avenue  \n",
       "10000       808 Columbus Avenue  \n",
       "100004          241 W 13 Street  \n",
       "100007     333 East 49th Street  \n",
       "100013    500 West 143rd Street  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "bathrooms: 浴室数量  \n",
    "bedrooms: 卧室数量  \n",
    "building_id: 房屋编号(可删除)  \n",
    "created: 创建时间  \n",
    "description: 房间信息描述  \n",
    "display_address: 显示地址  \n",
    "features: 该公寓的一些特色  \n",
    "interest_level: 用户感兴趣程度(目标值 y)  \n",
    "latitude: 纬度  \n",
    "listing_id: 列表编号(可删除)  \n",
    "longitude: 经度  \n",
    "manager_id: 管理员编号  \n",
    "photos: 照片(链接)  \n",
    "price: 租金  \n",
    "street_address: 街道地址"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 49352 entries, 10 to 99994\n",
      "Data columns (total 15 columns):\n",
      "bathrooms          49352 non-null float64\n",
      "bedrooms           49352 non-null int64\n",
      "building_id        49352 non-null object\n",
      "created            49352 non-null object\n",
      "description        49352 non-null object\n",
      "display_address    49352 non-null object\n",
      "features           49352 non-null object\n",
      "interest_level     49352 non-null object\n",
      "latitude           49352 non-null float64\n",
      "listing_id         49352 non-null int64\n",
      "longitude          49352 non-null float64\n",
      "manager_id         49352 non-null object\n",
      "photos             49352 non-null object\n",
      "price              49352 non-null int64\n",
      "street_address     49352 non-null object\n",
      "dtypes: float64(3), int64(3), object(9)\n",
      "memory usage: 6.0+ MB\n"
     ]
    }
   ],
   "source": [
    "train.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "训练集中有 49352 条记录  \n",
    "浮点型特征 3 个: bathrooms, latitude, longitude  \n",
    "整数型特征 3 个: bedrooms, listing_id, price  \n",
    "字符串型特征 8 个: building_id, created, description, display_address, features, manager_id, photos, street_address"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 74659 entries, 0 to 99999\n",
      "Data columns (total 14 columns):\n",
      "bathrooms          74659 non-null float64\n",
      "bedrooms           74659 non-null int64\n",
      "building_id        74659 non-null object\n",
      "created            74659 non-null object\n",
      "description        74659 non-null object\n",
      "display_address    74659 non-null object\n",
      "features           74659 non-null object\n",
      "latitude           74659 non-null float64\n",
      "listing_id         74659 non-null int64\n",
      "longitude          74659 non-null float64\n",
      "manager_id         74659 non-null object\n",
      "photos             74659 non-null object\n",
      "price              74659 non-null int64\n",
      "street_address     74659 non-null object\n",
      "dtypes: float64(3), int64(3), object(8)\n",
      "memory usage: 8.5+ MB\n"
     ]
    }
   ],
   "source": [
    "test.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "测试集中有 74659 条记录"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "训练集和测试集都**没有缺失值**."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 将类别型的标签 interest_level 编码为数字"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "low       34284\n",
       "medium    11229\n",
       "high       3839\n",
       "Name: interest_level, dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.interest_level.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 将类别型的标签 interest_level 编码为数字\n",
    "y_map = {'low':2, 'medium':1, 'high':0}\n",
    "train['interest_level'] = train['interest_level'].apply(lambda x: y_map[x])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "y_train = train['interest_level']\n",
    "# 删除无用特征 listing_id 及标签\n",
    "train.drop(['listing_id', 'interest_level'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "test_id = test['listing_id'] # 测试集保留, 生成提交文件时需要\n",
    "test.drop(['listing_id'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 数值型特征 (bathrooms, latitude, longitude, bedrooms, price) 处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>price</th>\n",
       "      <th>bathrooms</th>\n",
       "      <th>bedrooms</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>49352.000</td>\n",
       "      <td>49352.000</td>\n",
       "      <td>49352.000</td>\n",
       "      <td>49352.000</td>\n",
       "      <td>49352.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>3830.174</td>\n",
       "      <td>1.212</td>\n",
       "      <td>1.542</td>\n",
       "      <td>40.742</td>\n",
       "      <td>-73.956</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>22066.866</td>\n",
       "      <td>0.501</td>\n",
       "      <td>1.115</td>\n",
       "      <td>0.639</td>\n",
       "      <td>1.178</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>43.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>-118.271</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>2500.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>40.728</td>\n",
       "      <td>-73.992</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>3150.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>40.752</td>\n",
       "      <td>-73.978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>4100.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>2.000</td>\n",
       "      <td>40.774</td>\n",
       "      <td>-73.955</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>4490000.000</td>\n",
       "      <td>10.000</td>\n",
       "      <td>8.000</td>\n",
       "      <td>44.883</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            price  bathrooms  bedrooms  latitude  longitude\n",
       "count   49352.000  49352.000 49352.000 49352.000  49352.000\n",
       "mean     3830.174      1.212     1.542    40.742    -73.956\n",
       "std     22066.866      0.501     1.115     0.639      1.178\n",
       "min        43.000      0.000     0.000     0.000   -118.271\n",
       "25%      2500.000      1.000     1.000    40.728    -73.992\n",
       "50%      3150.000      1.000     1.000    40.752    -73.978\n",
       "75%      4100.000      1.000     2.000    40.774    -73.955\n",
       "max   4490000.000     10.000     8.000    44.883      0.000"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.loc[:,['price', 'bathrooms', 'bedrooms', 'latitude', 'longitude']].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.000     39422\n",
       "2.000      7660\n",
       "3.000       745\n",
       "1.500       645\n",
       "0.000       313\n",
       "2.500       277\n",
       "4.000       159\n",
       "3.500        70\n",
       "4.500        29\n",
       "5.000        20\n",
       "5.500         5\n",
       "6.000         4\n",
       "6.500         1\n",
       "10.000        1\n",
       "7.000         1\n",
       "Name: bathrooms, dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.bathrooms.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1    15752\n",
       "2    14623\n",
       "0     9475\n",
       "3     7276\n",
       "4     1929\n",
       "5      247\n",
       "6       46\n",
       "8        2\n",
       "7        2\n",
       "Name: bedrooms, dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.bedrooms.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "可以看出:  \n",
    "price 特征均值为 3830.174, 75%分位数为 4100, 标准差及最大值都很大, 明显有噪声, 考虑将大于 10000 的记录舍去.  \n",
    "bathrooms 特征最大值 10.0 可能记录有误. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 去噪声\n",
    "def remove_noise(df):\n",
    "    df = df[df['price'] < 10000]\n",
    "    \n",
    "    df.loc[df['bathrooms']==10.0, 'bathrooms']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 构造新特征\n",
    "# price_bathrooms: 单位 bathroom 的租金\n",
    "# price_bedrooms: 单位 bedroom 的租金\n",
    "# 都有 0 值, 计算时 rooms+1\n",
    "def create_price_room(df):\n",
    "    df['price_bathrooms'] = (df['price']) / (df['bathrooms']+1.0)\n",
    "    df['price_bedrooms'] = (df['price']) / (df['bedrooms']+1.0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 构造新特征\n",
    "# room_diff: bathrooms数 - bedrooms数\n",
    "# room_num: bathrooms数 + bedrooms数\n",
    "def create_room_diff_sum(df):\n",
    "    df['room_diff'] = df['bathrooms'] - df['bedrooms']\n",
    "    df['room_num'] = df['bathrooms'] + df['bedrooms']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### latitude, longtitude (经纬度) 特征处理\n",
    "聚类降维编码(用训练数据训练, 对训练数据和测试数据都做变换)到中心的距离"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def process_location_train(df):\n",
    "    train_location = df.loc[:, ['latitude', 'longitude']]\n",
    "    \n",
    "    # 聚类\n",
    "    kmeans_cluster = KMeans(n_clusters=20)\n",
    "    res = kmeans_cluster.fit(train_location)\n",
    "    res = kmeans_cluster.predict(train_location)\n",
    "    \n",
    "    df['cenroid'] = res\n",
    "    \n",
    "    # L1 距离\n",
    "    center = [train_location['latitude'].mean(), train_location['longitude'].mean()]\n",
    "    df['distance'] = abs(df['latitude']-center[0]) + abs(df['longitude']-center[1])\n",
    "    \n",
    "    return kmeans_cluster, center"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def process_location_test(df, kmeans_cluster, center):\n",
    "    test_location = df.loc[:, ['latitude', 'longitude']]\n",
    "    \n",
    "    # 聚类\n",
    "    res = kmeans_cluster.predict(test_location)\n",
    "    \n",
    "    df['cenroid'] = res\n",
    "    \n",
    "    # L1 距离\n",
    "    df['distance'] = abs(df['latitude']-center[0]) + abs(df['longitude']-center[1])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### building_id (房屋编号)特征处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 无用, 直接删除\n",
    "def process_building_id(df):\n",
    "    df.drop(['building_id'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### created (创建时间)特征处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def process_created_date(df):\n",
    "    df['Date'] = pd.to_datetime(df['created'])\n",
    "    df['Year'] = df['Date'].dt.year\n",
    "    df['Month'] = df['Date'].dt.month\n",
    "    df['Day'] = df['Date'].dt.day\n",
    "    df['Wday'] = df['Date'].dt.dayofweek\n",
    "    df['Yday'] = df['Date'].dt.dayofyear\n",
    "    df['hour'] = df['Date'].dt.hour\n",
    "    \n",
    "    df.drop(['Date', 'created'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### description (描述)特征处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 信息比较杂乱, 不好提取有用信息, 考虑暂时丢弃\n",
    "def process_description(df):\n",
    "    df = df.drop(['description'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### display_address (显示地址) 特征处理"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "定义高基数类别型特征编码函数 对这些特征进行均值编码（该特征值在每个类别的概率，即原来的一维特征变成了C-1维特征，C为标签类别数目）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 导入平均数编码模块\n",
    "import MeanEncoder"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def process_display_address_train_test(df_train, y_train, df_test):\n",
    "    # 训练样本数\n",
    "    n_train_samples = len(df_train.index)\n",
    "    # 合并训练集和测试集\n",
    "    df_train_test = pd.concat((df_train, df_test), axis=0)\n",
    "    \n",
    "    # 一般情况下，针对定性特征，我们只需要使用sklearn的LabelEncoder进行编码\n",
    "    lb = LabelEncoder()\n",
    "    lb.fit(list(df_train_test['display_address'].values))\n",
    "    df_train_test['display_address'] = lb.transform(list(df_train_test['display_address'].values))\n",
    "    \n",
    "    me = MeanEncoder(['display_address'], target_type='classification')\n",
    "    df_train_test = me.fit_transform(df_train_test, y_train)\n",
    "    \n",
    "    df_train_test.drop(['display_address'], axis=1, inplace=True)\n",
    "    \n",
    "    df_train = df_train_test[:n_train_samples, :]\n",
    "    df_test = df_train_test[n_train_samples:, :]\n",
    "    \n",
    "    return df_train, df_test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def process_display_address(df):\n",
    "    df.drop(['display_address'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### features (特色) 特征处理\n",
    "描述特征文字长度特征中单词的词频，相当于以数据集 features 中出现的词语为字典的 one-hot 编码（虽然是词频，但在这个任务中每个单词通常只出现一次）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def process_features_train_test(df_train, df_test):\n",
    "    n_train_samples = len(df_train.index)\n",
    "    \n",
    "    df_train_test = pd.concat((df_train, df_test), axis=0)\n",
    "    df_train_test['features2'] = df_train_test['features']\n",
    "    df_train_test['features2'] = df_train_test['features2'].apply(lambda x: ' '.join(x)) # 用空格隔开\n",
    "    \n",
    "    c_vect = CountVectorizer(stop_words='english', max_features=200, ngram_range=(1, 1), decode_error='ignore')\n",
    "    c_vect_sparse = c_vect.fit_transform(df_train_test['features2'])\n",
    "    c_vect_sparse_vols = c_vect.get_feature_names()\n",
    "    \n",
    "    df_train.drop(['features'], axis=1, inplace=True)\n",
    "    df_test.drop(['features'], axis=1, inplace=True)\n",
    "    \n",
    "    df_train_sparse = sparse.hstack([df_train, c_vect_sparse[:n_train_samples, :]]).tocsr()\n",
    "    df_test_sparse = sparse.hstack([df_test, c_vect_sparse[n_train_samples:, :]]).tocsr()\n",
    "    \n",
    "    # 常规DataFrame\n",
    "    tmp = pd.DataFrame(c_vect_sparse.toarray()[:n_train_samples, :], columns=c_vect_sparse_vols, index=df_train.index)\n",
    "    df_train = pd.concat([df_train, tmp], axis=1)\n",
    "    \n",
    "    tmp = pd.DataFrame(c_vect_sparse.toarray()[n_train_samples:, :], columns=c_vect_sparse_vols, index=df_test.index)\n",
    "    df_test = pd.concat([df_test, tmp], axis=1)\n",
    "    \n",
    "    return df_train_sparse, df_test_sparse, df_train, df_test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def process_features_test(df, c_vect):\n",
    "    df['features2'] = df['features']\n",
    "    df['features2'] = df['features2'].apply(lambda x: ' '.join(x))\n",
    "    \n",
    "    c_vect_sparse = c_vect.transform(df['features2'])\n",
    "    c_vect_sparse_vols = c_vect.get_feature_names()\n",
    "    \n",
    "    df.drop(['features', 'features2'], axis=1, inplace=True)\n",
    "    \n",
    "    df_sparse = sparse.hstack([df, c_vect_sparse]).tocsr()\n",
    "    \n",
    "    tmp = pd.DataFrame(c_vect_sparse.toarray(), columns=c_vect_sparse_vols, index=df.index)\n",
    "    df = pd.concat([df, tmp], axis=1)\n",
    "    \n",
    "    return df_sparse, df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### manager_id (管理员编号) 特征处理\n",
    "将 manager 分为几个等级: top 1%, 2%, 5%, 10%, 15%, 20%, 25%, 30%, 50%"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "e6472c7237327dd3903b3d6f6a94515a    2533\n",
       "6e5c10246156ae5bdcd9b487ca99d96a     711\n",
       "8f5a9c893f6d602f4953fcc0b8e6e9b4     410\n",
       "62b685cc0d876c3a1a51d63a0d6a8082     402\n",
       "cb87dadbca78fad02b388dc9e8f25a5b     373\n",
       "9df32cb8dda19d3222d66e69e258616b     330\n",
       "b7de4cb395920136663132057fa89d84     320\n",
       "2aa9bfa5f67ed9997ea341dee8a3a271     316\n",
       "ad3d8ddc52c7e0859b5c6c7f7949c3bd     305\n",
       "c9c33695ee2a2f818e9f1d8f7d1c4b39     299\n",
       "1fb46c4a72bcf764ac35fc23f394760d     289\n",
       "d2bce61e0e0079ebdc8c281e415e045b     276\n",
       "5599e962719af3ccc2976855c2d5893c     256\n",
       "aa9e353a6b43b125cbc89cb751090a9e     246\n",
       "62826f3ae01f2ddc93b9cd28c659ab2b     233\n",
       "8b53ccf4338806ab1be3dd0267711649     222\n",
       "612a00076aefe8c98d1df4835640c74b     214\n",
       "dbbb6b990661b1e507a387f019bcb1a0     212\n",
       "8262449f40e9117f7a9ea49b4a333993     210\n",
       "b209e2c4384a64cc307c26759ee0c651     210\n",
       "fc81b75568d3655a922523cb0d77ea5b     206\n",
       "d1737922fe92ccb0dc37ba85589e6415     198\n",
       "1067e078446a7897d2da493d2f741316     191\n",
       "501b52559fd21b32808030a0f4fb1a26     170\n",
       "02e17b21a1814fb10336b2ee8ceb3e79     170\n",
       "381040570cc863b743a9fce1a6ae9724     169\n",
       "30a7951cfc21240e8c46b2d079d292e5     169\n",
       "eb5a6c4c2eb6f3e2a085c477afd5a815     168\n",
       "d399821d8583b8421a55370c6eb15f6f     167\n",
       "9d32b720e26a351b951c8f78f72f2fec     164\n",
       "                                    ... \n",
       "71415b6b717de88495aa2f6f7dea089a       1\n",
       "1a4b1d33df0cff852a519b597f1f28b7       1\n",
       "8e6ff67f7b073334fc92114c4f0ce955       1\n",
       "8e89e914cc960b5d63c3bfda336856f6       1\n",
       "5d20991cf9a42e459d8d12aa4cd2232f       1\n",
       "2ddf1f8d0718a37d11ffc45f27dd4490       1\n",
       "9cb1d7ed67da245c18eb1c6d8a725e14       1\n",
       "199c5250ff3b547121295cfbfe24145b       1\n",
       "ef1b0ffe2b77c0040520f7e5560d08a5       1\n",
       "93b0c74607cdfdada256cd01b1884d71       1\n",
       "b6a7bb7cd5a4abec7c7aac0e8da7f9ee       1\n",
       "af8aec8550da074b6abecf06cca13e81       1\n",
       "a7d3ff45baf5a8b9f57f4810ef94fe44       1\n",
       "b0af549df6da29d2284dcecc2746663e       1\n",
       "2cce9b724d79fff0453b68d6600ab734       1\n",
       "8fd9e5584348712a598f74ea341d5df6       1\n",
       "485f563d1241adfbda665a93f5ae30d1       1\n",
       "9fed23d567bf23b9c133bd88200018b8       1\n",
       "f4f1bcf11e75c245a7565847b6b7b147       1\n",
       "67d9eb08db9724a3b204601e6905a84f       1\n",
       "936cd0688ac04e1300e63ce8792981b4       1\n",
       "f5ff5a1ce3d0ea87779432378721519f       1\n",
       "c1da260cb050d17d1c8fc27b3d9eb3e1       1\n",
       "cd42e7e45403d8ce6681f1ba4a62850b       1\n",
       "f7050329f1964a0a971703263427d524       1\n",
       "7a9463349cbe429662215d14ba436b5b       1\n",
       "a4d41aee7c9c114309e98a5986641388       1\n",
       "f86873f49ff488d135afa75dc7eb0e06       1\n",
       "94c0b47708917578138c1ad3ee3499cd       1\n",
       "fac28b9bc5125bd99a49ec2416cc88d6       1\n",
       "Name: manager_id, Length: 3481, dtype: int64"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train['manager_id'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "value_counts()结果按每个值出现的次数多少排序, 对应现实中 top 等级越高, 人数越少. 次数的分布基本可代表 manager 等级人数的分布."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def process_manager_id(df):\n",
    "    managers_count = df['manager_id'].value_counts()\n",
    "    \n",
    "    # np.percentile() 计算分位数\n",
    "    df['top_1_manager'] = df['manager_id'].apply(lambda x: 1 if x in managers_count.index.values[\n",
    "        managers_count.values >= np.percentile(managers_count.values, 99)\n",
    "    ] else 0)\n",
    "    df['top_2_manager'] = df['manager_id'].apply(lambda x: 1 if x in managers_count.index.values[\n",
    "        managers_count.values >= np.percentile(managers_count.values, 98)\n",
    "    ] else 0)\n",
    "    df['top_5_manager'] = df['manager_id'].apply(lambda x: 1 if x in managers_count.index.values[\n",
    "        managers_count.values >= np.percentile(managers_count.values, 95)\n",
    "    ] else 0)\n",
    "    df['top_10_manager'] = df['manager_id'].apply(lambda x: 1 if x in managers_count.index.values[\n",
    "        managers_count.values >= np.percentile(managers_count.values, 90)\n",
    "    ] else 0)\n",
    "    df['top_15_manager'] = df['manager_id'].apply(lambda x: 1 if x in managers_count.index.values[\n",
    "        managers_count.values >= np.percentile(managers_count.values, 85)\n",
    "    ] else 0)\n",
    "    df['top_20_manager'] = df['manager_id'].apply(lambda x: 1 if x in managers_count.index.values[\n",
    "        managers_count.values >= np.percentile(managers_count.values, 80)\n",
    "    ] else 0)\n",
    "    df['top_25_manager'] = df['manager_id'].apply(lambda x: 1 if x in managers_count.index.values[\n",
    "        managers_count.values >= np.percentile(managers_count.values, 75)\n",
    "    ] else 0)\n",
    "    df['top_30_manager'] = df['manager_id'].apply(lambda x: 1 if x in managers_count.index.values[\n",
    "        managers_count.values >= np.percentile(managers_count.values, 70)\n",
    "    ] else 0)\n",
    "    df['top_50_manager'] = df['manager_id'].apply(lambda x: 1 if x in managers_count.index.values[\n",
    "        managers_count.values >= np.percentile(managers_count.values, 50)\n",
    "    ] else 0)\n",
    "    \n",
    "    df.drop(['manager_id'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### photos (照片) 特征处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def process_photos(df):\n",
    "    # 构造新特征\"照片数\", 丢弃原特征\n",
    "    df['photos_count'] = df['photos'].apply(lambda x: len(x))\n",
    "    df.drop(['photos'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### street_address (街道地址) 特征处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 和 display_address 信息冗余, 丢弃\n",
    "def process_street_address(df):\n",
    "    df = df.drop(['street_address'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 对训练样本做特征工程"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "remove_noise(train)\n",
    "create_price_room(train)\n",
    "create_room_diff_sum(train)\n",
    "\n",
    "process_created_date(train)\n",
    "\n",
    "process_description(train)\n",
    "\n",
    "process_manager_id(train)\n",
    "\n",
    "process_building_id(train)\n",
    "\n",
    "process_photos(train)\n",
    "\n",
    "process_street_address(train)\n",
    "\n",
    "process_display_address(train)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 对测试样本做特征工程"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "remove_noise(test)\n",
    "create_price_room(test)\n",
    "create_room_diff_sum(test)\n",
    "\n",
    "process_created_date(test)\n",
    "\n",
    "process_description(test)\n",
    "\n",
    "process_manager_id(test)\n",
    "\n",
    "process_building_id(test)\n",
    "\n",
    "process_photos(test)\n",
    "\n",
    "process_street_address(test)\n",
    "\n",
    "process_display_address(test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "X_train_sparse, X_test_sparse, train, test = process_features_train_test(train, test)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 特征处理结果存为文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "train = pd.concat([train, y_train], axis=1)\n",
    "test = pd.concat([test_id, test], axis=1)\n",
    "train.to_csv('FE_train_RentListingInquries.csv', index=False)\n",
    "test.to_csv('FE_test_RentListingInquries.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 49352 entries, 10 to 99994\n",
      "Columns: 226 entries, bathrooms to interest_level\n",
      "dtypes: float64(7), int64(219)\n",
      "memory usage: 85.5 MB\n"
     ]
    }
   ],
   "source": [
    "train.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 74659 entries, 0 to 99999\n",
      "Columns: 226 entries, listing_id to work\n",
      "dtypes: float64(7), int64(219)\n",
      "memory usage: 129.3 MB\n"
     ]
    }
   ],
   "source": [
    "test.info()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
